﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using IToursDataService;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using IToursDataService.Models;

namespace ToursDataService
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“HotelOrderCtrl”。
    public class HotelOrderCtrl : IHotelOrderCtrl
    {
        /// <summary>
        /// 根据团号获取指定团中各类房间的总数
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public DataTable GetHotelOrderRoomNum(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //            string sql = string.Format(@"SELECT Sum(BigBedRoomNum) as BigBedRoomNums,
            //                                        Sum(SingleRoomNum) as SingleRoomNums,
            //                                        Sum(DoubleRoomNum) as DoubleRoomNums,
            //                                        Sum(ThreeRoomNum) as ThreeRoomNums,
            //                                        Sum(AccompanyRoomNum) as AccompanyRoomNums,
            //                                        (Sum(BigBedRoomNum)+Sum(SingleRoomNum)+Sum(DoubleRoomNum)+Sum(ThreeRoomNum)+Sum(AccompanyRoomNum)) as TotalRoomNum 
            //                                        FROM fee_HotelOrder WHERE TourgroupID='{0}'", tourGroupId);
            string sql = string.Format(@"SELECT TOP 1 BigBedRoomNum,
                                        SingleRoomNum,
                                        DoubleRoomNum,
                                        ThreeRoomNum,
                                        AccompanyRoomNum,
                                        BreakMethod,
                                        (BigBedRoomNum +SingleRoomNum+DoubleRoomNum+ThreeRoomNum) as TotalRoomNum 
                                        FROM fee_HotelOrder WHERE TourgroupID='{0}'", tourGroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取指定团号的宾馆预订信息，用于打印
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public DataTable GetHotelOrderForPrint(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string sql = string.Format(@"SELECT 
                                                h.[ScheduleMethod], 
                                                h.[ArrivalTime], 
                                                h.[LeaveTime],
                                                h.[StayOver],
                                                h.[SpecialRoom],
                                                h.[BreakMethod],
                                                e.[ProviderName]
                                                 FROM fee_HotelOrder h,fee_Expense e WHERE h.ExpenseID=e.ExpenseID AND h.TourgroupID='{0}'AND  h.[ScheduleMethod]='{1}'order by h.[ArrivalTime]", tourGroupId, HotelScheduleMethodEnum.自订);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 删除指定计划号的宾馆预定信息
        /// </summary>
        /// <param name="tourGroupScheduleID"></param>
        /// <returns></returns>
        public int DeleteHotelOrder(int tourGroupScheduleID)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format("select expenseid from fee_HotelOrder where TourGroupScheduleID={0}", tourGroupScheduleID);
            DbCommand dbCmd = db.GetSqlStringCommand(sql);
            object expenseObj = db.ExecuteScalar(dbCmd);

            if (expenseObj == null)
                return 0;

            sql = string.Format(@"DELETE FROM fee_HotelOrder WHERE TourGroupScheduleID={0};
                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和地接社相关内容
                                         tourGroupScheduleID,
                                         expenseObj);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
    }
}
